/*
 *    GeoTools - The Open Source Java GIS Toolkit
 *    http://geotools.org
 *
 *    (C) 2002-2015, Open Source Geospatial Foundation (OSGeo)
 *
 *    This library is free software; you can redistribute it and/or
 *    modify it under the terms of the GNU Lesser General Public
 *    License as published by the Free Software Foundation;
 *    version 2.1 of the License.
 *
 *    This library is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *    Lesser General Public License for more details.
 */
package org.geotools.data.h2;

import geodb.GeoDB;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Map;
import java.util.UUID;
import java.util.logging.Level;
import org.geotools.api.feature.simple.SimpleFeatureType;
import org.geotools.api.feature.type.AttributeDescriptor;
import org.geotools.api.feature.type.GeometryDescriptor;
import org.geotools.api.feature.type.PropertyDescriptor;
import org.geotools.api.referencing.FactoryException;
import org.geotools.api.referencing.crs.CoordinateReferenceSystem;
import org.geotools.geometry.jts.Geometries;
import org.geotools.jdbc.JDBCDataStore;
import org.geotools.jdbc.SQLDialect;
import org.geotools.referencing.CRS;
import org.geotools.util.factory.Hints;
import org.locationtech.jts.geom.Envelope;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.geom.GeometryFactory;
import org.locationtech.jts.geom.LineString;
import org.locationtech.jts.geom.MultiLineString;
import org.locationtech.jts.geom.MultiPoint;
import org.locationtech.jts.geom.MultiPolygon;
import org.locationtech.jts.geom.Point;
import org.locationtech.jts.geom.Polygon;
import org.locationtech.jts.io.ParseException;
import org.locationtech.jts.io.WKBReader;
import org.locationtech.jts.io.WKTWriter;

/**
 * Delegate for {@link H2DialectBasic} and {@link H2DialectPrepared} which implements the common
 * parts of the dialect api.
 *
 * @author Justin Deoliveira, OpenGEO
 */
public class H2Dialect extends SQLDialect {

    public static String H2_SPATIAL_INDEX = "org.geotools.data.h2.spatialIndex";

    public H2Dialect(JDBCDataStore dataStore) {
        super(dataStore);
    }

    @Override
    public String getNameEscape() {
        return "\"";
    }

    @Override
    public void initializeConnection(Connection cx) throws SQLException {
        // spatialize the database
        GeoDB.InitGeoDB(cx);
    }

    @Override
    public boolean includeTable(String schemaName, String tableName, Connection cx)
            throws SQLException {
        if ("_GEODB".equals(tableName) || tableName.endsWith("_HATBOX")) {
            return false;
        }
        if ("GEOMETRY_COLUMNS".equalsIgnoreCase(tableName)) {
            return false;
        }

        return true;
    }

    @Override
    public Class<?> getMapping(ResultSet columnMetaData, Connection cx) throws SQLException {

        String typeName = columnMetaData.getString("TYPE_NAME");
        if ("UUID".equalsIgnoreCase(typeName)) {
            return UUID.class;
        } else if ("BLOB".equalsIgnoreCase(typeName) || "VARBINARY".equalsIgnoreCase(typeName)) {
            String schemaName = columnMetaData.getString("TABLE_SCHEM");
            String tableName = columnMetaData.getString("TABLE_NAME");
            String columnName = columnMetaData.getString("COLUMN_NAME");

            // look up in geometry columns table
            StringBuffer sql = new StringBuffer("SELECT type FROM geometry_columns WHERE ");
            if (schemaName != null) {
                sql.append("f_table_schema = '").append(schemaName).append("'").append(" AND ");
            }
            sql.append("f_table_name = '").append(tableName).append("' AND ");
            sql.append("f_geometry_column = '").append(columnName).append("'");

            Statement st = cx.createStatement();
            try {
                LOGGER.fine(sql.toString());
                ResultSet rs = st.executeQuery(sql.toString());
                try {
                    if (rs.next()) {
                        String type = rs.getString(1);
                        Geometries g = Geometries.getForName(type);
                        if (g != null) {
                            return g.getBinding();
                        }
                        LOGGER.warning("Geometry type " + type + " not supported.");
                    }
                } finally {
                    dataStore.closeSafe(rs);
                }
            } finally {
                dataStore.closeSafe(st);
            }

            // not a geometry blob, return byte[].class
            return byte[].class;
        }

        // do a check for a column remark which marks this as a geometry
        // do this mostly for backwards compatability
        String remark = columnMetaData.getString("REMARKS");
        if (remark != null) {
            Geometries g = Geometries.getForName(remark);
            if (g != null) {
                return g.getBinding();
            }
        }

        return null;
    }

    @Override
    public void encodePostColumnCreateTable(AttributeDescriptor att, StringBuffer sql) {
        if (att instanceof GeometryDescriptor) {
            // try to narrow down the type with a comment
            Class binding = att.getType().getBinding();
            if (isConcreteGeometry(binding)) {
                sql.append(" COMMENT '").append(binding.getSimpleName().toUpperCase()).append("'");
            }
        }
    }

    @Override
    public void registerSqlTypeToClassMappings(Map<Integer, Class<?>> mappings) {
        super.registerSqlTypeToClassMappings(mappings);

        // clear BLOB, we handle them custom
        mappings.remove(Types.BLOB);
    }

    @Override
    public void registerClassToSqlMappings(Map<Class<?>, Integer> mappings) {
        super.registerClassToSqlMappings(mappings);
        mappings.put(Geometry.class, Types.BLOB);
    }

    @Override
    public void postCreateTable(String schemaName, SimpleFeatureType featureType, Connection cx)
            throws SQLException {

        Statement st = cx.createStatement();
        try {
            String tableName = featureType.getTypeName();
            schemaName = schemaName != null ? schemaName : "PUBLIC";
            // post process the feature type and set up constraints based on geometry type
            for (PropertyDescriptor ad : featureType.getDescriptors()) {
                if (ad instanceof GeometryDescriptor) {
                    GeometryDescriptor gd = (GeometryDescriptor) ad;
                    @SuppressWarnings("unchecked")
                    Class<? extends Geometry> binding =
                            (Class<? extends Geometry>) ad.getType().getBinding();
                    String propertyName = ad.getName().getLocalPart();

                    // create a spatial index
                    int epsg = -1;
                    try {
                        CoordinateReferenceSystem crs = gd.getCoordinateReferenceSystem();
                        if (crs != null) {
                            Integer code = CRS.lookupEpsgCode(crs, true);
                            epsg = code != null ? code : -1;
                        } else {
                            LOGGER.warning("Column " + gd.getLocalName() + " has no crs");
                        }
                    } catch (FactoryException e) {
                        LOGGER.log(Level.FINER, "Unable to look epsg code", e);
                    }

                    StringBuffer sql = new StringBuffer();
                    sql.append("CALL AddGeometryColumn(");
                    sql.append("'").append(schemaName).append("'");

                    sql.append(", '").append(tableName).append("'");
                    sql.append(", '").append(gd.getLocalName()).append("'");
                    sql.append(", ").append(epsg);
                    sql.append(", '")
                            .append(Geometries.getForBinding(binding).getName())
                            .append("'");
                    sql.append(", ").append(2); // TODO: dimension
                    sql.append(")");

                    LOGGER.fine(sql.toString());
                    st.execute(sql.toString());

                    if (epsg != -1) {
                        sql = new StringBuffer();
                        sql.append("CALL CreateSpatialIndex(");
                        if (schemaName == null) {
                            sql.append("NULL");
                        } else {
                            sql.append("'").append(schemaName).append("'");
                        }

                        sql.append(",'").append(tableName).append("'");
                        sql.append(",'").append(propertyName).append("'");
                        sql.append(",'").append(epsg).append("')");

                        LOGGER.fine(sql.toString());
                        st.execute(sql.toString());
                    }
                }
            }
        } finally {
            dataStore.closeSafe(st);
        }
    }

    @Override
    public void postCreateFeatureType(
            SimpleFeatureType featureType,
            DatabaseMetaData metadata,
            String schemaName,
            Connection cx)
            throws SQLException {
        // figure out if the table has a spatial index and mark the feature type as so
        if (featureType.getGeometryDescriptor() == null) {
            return;
        }
        String idxTableName = featureType.getTypeName() + "_HATBOX";
        ResultSet rs =
                metadata.getTables(
                        null,
                        dataStore.escapeNamePattern(metadata, schemaName),
                        dataStore.escapeNamePattern(metadata, idxTableName),
                        new String[] {"TABLE"});
        try {
            if (rs.next()) {
                featureType
                        .getGeometryDescriptor()
                        .getUserData()
                        .put(H2_SPATIAL_INDEX, idxTableName);
            }
        } finally {
            dataStore.closeSafe(rs);
        }
    }

    @Override
    public void preDropTable(String schemaName, SimpleFeatureType featureType, Connection cx)
            throws SQLException {
        String tableName = featureType.getTypeName();
        Statement st = cx.createStatement();

        try {
            // drop the spatial index
            StringBuffer sql = new StringBuffer();
            sql.append("CALL DropSpatialIndex(");
            if (schemaName == null) {
                sql.append("NULL");
            } else {
                sql.append("'").append(schemaName).append("'");
            }

            sql.append(",'").append(tableName).append("')");
            LOGGER.fine(sql.toString());

            try {
                st.execute(sql.toString());
            } catch (SQLException e) {
                // table may not have had a spatial index
                // TODO: do a better check
                LOGGER.log(Level.FINE, e.getLocalizedMessage(), e);
            }

            // remove the geometry metadata
            sql = new StringBuffer();
            sql.append("CALL DropGeometryColumns(");
            if (schemaName == null) {
                sql.append("NULL");
            } else {
                sql.append("'").append(schemaName).append("'");
            }

            sql.append(",'").append(tableName).append("')");
            LOGGER.fine(sql.toString());

            st.execute(sql.toString());
        } finally {
            dataStore.closeSafe(st);
        }
    }

    boolean isConcreteGeometry(Class binding) {
        return Point.class.isAssignableFrom(binding)
                || LineString.class.isAssignableFrom(binding)
                || Polygon.class.isAssignableFrom(binding)
                || MultiPoint.class.isAssignableFrom(binding)
                || MultiLineString.class.isAssignableFrom(binding)
                || MultiPolygon.class.isAssignableFrom(binding);
    }

    @Override
    public Integer getGeometrySRID(
            String schemaName, String tableName, String columnName, Connection cx)
            throws SQLException {

        // first try getting from table metadata
        int srid = GeoDB.GetSRID(cx, schemaName, tableName);
        if (srid > -1) {
            return srid;
        }

        // try grabbing directly from a geometry
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ST_SRID(");
        encodeColumnName(null, columnName, sql);
        sql.append(") ");
        sql.append("FROM ");

        if (schemaName != null) {
            encodeTableName(schemaName, sql);
            sql.append(".");
        }

        encodeSchemaName(tableName, sql);
        sql.append(" WHERE ");
        encodeColumnName(null, columnName, sql);
        sql.append(" is not null LIMIT 1");

        dataStore.getLogger().fine(sql.toString());
        Statement st = cx.createStatement();

        try {
            ResultSet rs = st.executeQuery(sql.toString());

            try {
                if (rs.next()) {
                    return Integer.valueOf(rs.getInt(1));
                } else {
                    // could not find o
                    return null;
                }
            } finally {
                dataStore.closeSafe(rs);
            }
        } finally {
            dataStore.closeSafe(st);
        }
    }

    @Override
    public void encodeGeometryEnvelope(String tableName, String geometryColumn, StringBuffer sql) {
        // TODO: change spatialdbbox to use envelope
        sql.append("ST_Envelope(");
        encodeColumnName(null, geometryColumn, sql);
        sql.append(")");
    }

    @Override
    public Envelope decodeGeometryEnvelope(ResultSet rs, int column, Connection cx)
            throws SQLException, IOException {

        // TODO: change spatialdb in a box to return ReferencedEnvelope
        return (Envelope) rs.getObject(column);
    }

    public void encodeGeometryValue(Geometry value, int srid, StringBuffer sql) throws IOException {
        if (value == null || value.isEmpty()) {
            sql.append("ST_GeomFromText ('");
            sql.append(new WKTWriter().write(value));
            sql.append("',");
            sql.append(srid);
            sql.append(")");
        } else {
            sql.append("NULL");
        }
    }

    @Override
    public Geometry decodeGeometryValue(
            GeometryDescriptor descriptor,
            ResultSet rs,
            String column,
            GeometryFactory factory,
            Connection cx,
            Hints hints)
            throws IOException, SQLException {
        byte[] bytes = rs.getBytes(column);

        if (bytes == null) {
            return null;
        }

        try {
            return new WKBReader(factory).read(bytes);
        } catch (ParseException e) {
            throw (IOException) new IOException().initCause(e);
        }

        // return JTS.geometryFromBytes( bytes );
    }

    @Override
    public void encodePrimaryKey(String column, StringBuffer sql) {
        encodeColumnName(null, column, sql);
        sql.append(" int AUTO_INCREMENT(1) PRIMARY KEY");
    }

    @Override
    public String getSequenceForColumn(
            String schemaName, String tableName, String columnName, Connection cx)
            throws SQLException {

        String sequenceName = tableName + "_" + columnName + "_SEQUENCE";

        // sequence names have to be upper case to select values from them
        sequenceName = sequenceName.toUpperCase();
        Statement st = cx.createStatement();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT * FROM INFORMATION_SCHEMA.SEQUENCES ");
            sql.append("WHERE SEQUENCE_NAME = '").append(sequenceName).append("'");

            dataStore.getLogger().fine(sql.toString());
            ResultSet rs = st.executeQuery(sql.toString());
            try {
                if (rs.next()) {
                    return sequenceName;
                }
            } finally {
                dataStore.closeSafe(rs);
            }
        } finally {
            dataStore.closeSafe(st);
        }

        return null;
    }

    @Override
    public Object getNextSequenceValue(String schemaName, String sequenceName, Connection cx)
            throws SQLException {

        Statement st = cx.createStatement();
        try {
            String sql = "SELECT " + encodeNextSequenceValue(schemaName, sequenceName);
            dataStore.getLogger().fine(sql);
            ResultSet rs = st.executeQuery(sql);
            try {
                if (!rs.next()) {
                    throw new SQLException("Could not find a next value for sequence");
                }
                return rs.getInt(1);
            } finally {
                dataStore.closeSafe(rs);
            }

        } finally {
            dataStore.closeSafe(st);
        }
    }

    @Override
    public String encodeNextSequenceValue(String schemaName, String sequenceName) {
        return "NEXTVAL('" + sequenceName + "')";
    }

    @Override
    @SuppressWarnings("PMD.CheckResultSet")
    public Object getNextAutoGeneratedValue(
            String schemaName, String tableName, String columnName, Connection cx)
            throws SQLException {

        try (Statement st = cx.createStatement()) {
            // figure out which sequence to query
            String sequence = null;
            try (ResultSet rs =
                    st.executeQuery(
                            "SELECT b.COLUMN_DEFAULT "
                                    + " FROM INFORMATION_SCHEMA.INDEXES A, INFORMATION_SCHEMA.COLUMNS B "
                                    + "WHERE a.TABLE_NAME = b.TABLE_NAME "
                                    + " AND a.COLUMN_NAME = b.COLUMN_NAME "
                                    + " AND a.TABLE_NAME = '"
                                    + tableName
                                    + "' "
                                    + " AND a.COLUMN_NAME = '"
                                    + columnName
                                    + "' "
                                    + " AND a.PRIMARY_KEY = TRUE")) {
                if (!rs.next()) {
                    throw new SQLException("Could not grab the next auto generated value");
                }

                String string = rs.getString(1);
                sequence = string.substring(string.indexOf("SYSTEM_SEQUENCE"), string.length() - 1);
            }

            try (ResultSet rs =
                    schemaName != null
                            ? st.executeQuery(
                                    "SELECT CURRVAL('" + schemaName + "','" + sequence + "')")
                            : st.executeQuery("SELECT CURRVAL('" + sequence + "')")) {
                rs.next();

                int value = rs.getInt(1);

                return Integer.valueOf(value + 1);
            }
        }
    }

    @Override
    public boolean isLimitOffsetSupported() {
        return true;
    }

    @Override
    public void applyLimitOffset(StringBuffer sql, int limit, int offset) {
        if (limit >= 0 && limit < Integer.MAX_VALUE) {
            sql.append(" LIMIT " + limit);
            if (offset > 0) {
                sql.append(" OFFSET " + offset);
            }
        } else if (offset > 0) {
            // H2 pretends to have limit specified along with offset
            sql.append(" LIMIT " + Integer.MAX_VALUE);
            sql.append(" OFFSET " + offset);
        }
    }

    @Override
    protected boolean supportsSchemaForIndex() {
        return true;
    }

    @Override
    public void registerSqlTypeToSqlTypeNameOverrides(Map<Integer, String> overrides) {
        overrides.put(Types.BLOB, "BYTEA");
    }

    @Override
    public boolean canGroupOnGeometry() {
        return true;
    }
}
